﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WinHBM.Core.DB
{
    public class SQLiteHelper
    {

        private static string path = System.Environment.CurrentDirectory + @"/Data/";
        private static string dbName = "WinHBM.sqlite";
        private static readonly log4net.ILog Logger = log4net.LogManager.GetLogger(typeof(SQLiteHelper));

        private static SQLiteHelper _instance;

        //private SQLiteConnection connection;

        public static SQLiteHelper Instance
        {
            get
            {
                if (_instance == null)
                {
                    _instance = new SQLiteHelper();
                }
                return _instance;
            }
        }

        public SQLiteConnection NewConnection
        {
            get
            {
                /*if(connection == null)
                {
                    connection = new SQLiteConnection("data source=" + path + dbName);
                    //connection.Open();
                }
                return connection;*/
                SQLiteConnection connection = new SQLiteConnection("data source=" + path + dbName);
                
                connection.Open();
                return connection;
            }
        }

        public static void install()
        {
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            string databaseFileName = path + dbName;
            if (!File.Exists(databaseFileName))
            {
                try
                {
                    SQLiteConnection.CreateFile(databaseFileName);
                    CreateTable();
                }
                catch (Exception e)
                {
                    Logger.Error("创建数据库文件出错:"+e);
                }
            }
        }
        //创建表
        public static void CreateTable()
        {
            SQLiteHelper db = SQLiteHelper.Instance;
            string sql = "CREATE TABLE IF NOT EXISTS babyInfo(id INTEGER PRIMARY KEY AUTOINCREMENT,babyId INTEGER ,classId INTEGER,name TEXT,avatar TEXT,red INTEGER)";
            db.ExecuteNonQuery(sql);
            sql = "CREATE TABLE IF NOT EXISTS flowerLog(id INTEGER PRIMARY KEY AUTOINCREMENT,teacherId INTEGER ,babyId INTEGER ,time TEXT,type TEXT)";
            db.ExecuteNonQuery(sql);

        }

        protected SQLiteParameter[] buildSqlParams(Dictionary<string,string> dict,string flag="")
        {
            SQLiteParameter[] paras = new SQLiteParameter[dict.Count];
            int i = 0;
            foreach (var item in dict)
            {
                paras[i] = new SQLiteParameter("@"+flag + item.Key, item.Value);
                i++;
            }
            return paras;
        }

        
        protected string[] makePlaceholder(string[] keys)
        {
            for (int i = 0; i < keys.Length; i++)
            {
                keys[i] = "@" + keys[i];
            }
            return keys;
        }

        protected string[] makeKeyValueString(string[] keys,string flag = "")
        {
            for (int i = 0; i < keys.Length; i++)
            {
                keys[i] = keys[i] + "=@" + flag + keys[i];
            }
            return keys;
        }


        public int insert(string table,Dictionary<string,string> data)
        {
            string columns = string.Join(",", data.Keys);
            string placeholder = string.Join(",", makePlaceholder(data.Keys.ToArray()));
            string sql = "insert into " + table + " (" + columns + ") values " + "(" + placeholder + ")";
            return ExecuteNonQuery(sql,this.buildSqlParams(data));
        }


        public int update(string table, Dictionary<string, string> data, Dictionary<string, string> condition)
        {
            //string columns = string.Join(",", data.Keys);
            string sets = string.Join(",", makeKeyValueString(data.Keys.ToArray(),"s_"));
            string cond = string.Join(" and ", makeKeyValueString(condition.Keys.ToArray(), "w_"));
            string sql = "update " + table + " set " + sets + " where " + cond;

            List<SQLiteParameter> paramList = new List<SQLiteParameter>();
            paramList.AddRange(this.buildSqlParams(data, "s_"));
            paramList.AddRange(this.buildSqlParams(condition, "w_"));

            return this.ExecuteNonQuery(sql, paramList.ToArray());
        }

        internal void insert()
        {
            throw new NotImplementedException();
        }

        public int delete(string table, Dictionary<string, string> condition)
        {
            string cond = string.Join(" and ", makeKeyValueString(condition.Keys.ToArray()));
            string sql = "delete from " + table +  " where " + cond;
            SQLiteParameter[] paras = this.buildSqlParams(condition);
            return this.ExecuteNonQuery(sql, paras);
        }

        public int deleteById(string table,string id)
        {
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras["id"] = id;
            return this.delete(table, paras);
        }




        public List<Dictionary<string,string>> find(string table,Dictionary<string,string> condition,string orderBy)
        {
            List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
            SQLiteParameter[] paras = null;
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from " + table);
            if (condition != null && condition.Count > 0)
            {
                sb.Append(" where ");
                paras = this.buildSqlParams(condition);
                foreach (var item in condition)
                {
                    sb.Append(item.Key + "=@" + item.Key);
                    if(item.Key != condition.Last().Key)
                    {
                        sb.Append(" and ");
                    }
                }
            }

            if(orderBy != null)
            {
                sb.Append(" order by " + orderBy);
            }

            return this.ExecuteQuery(sb.ToString(), paras);
        }

        public List<Dictionary<string, string>> find(string table, Dictionary<string, string> condition)
        {
            return this.find(table,condition,null);
        }

        public List<Dictionary<string, string>> findAll(string table)
        {
            return this.find(table,null);
        }

        public Dictionary<string, string> findById(string table,string id)
        {
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras["id"] = id;
            List<Dictionary<string, string>> list = this.find(table, paras);
            if(list.Count == 0)
            {
                return null;
            }
            return list.First();
        }
        ///
        /// 该方法执行传入的增删改SQL语句
        /// 要执行的增删改SQL语句
        /// 返回更新的记录数
        protected int ExecuteNonQuery(string sql)
        {
            return this.ExecuteNonQuery(sql, null);
        }

        protected int ExecuteNonQuery(string sql, SQLiteParameter[] paras)
        {
            int res;
            SQLiteConnection connection = null;
            try
            {
                connection = this.NewConnection;
                SQLiteCommand cmd = new SQLiteCommand(sql, connection);
                if(paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                res = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                res = -1;
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
            return res;
        }

        ///
        /// 执行带参数的SQL查询语句
        ///
        /// SQL查询语句
        /// 参数集合
        /// 返回数据集合
        protected List<Dictionary<string, string>> ExecuteQuery(string sql, SQLiteParameter[] paras)
        {
            SQLiteConnection connection = null;

            try
            {
                connection = this.NewConnection;
                SQLiteCommand cmd = new SQLiteCommand(sql, connection);
                if(paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                
                SQLiteDataReader sr = cmd.ExecuteReader();
                List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
                while (sr.Read())
                {
                    Dictionary<string, string> dic = new Dictionary<string, string>();
                    for (int i = 0; i < sr.FieldCount; i++)
                    {
                        dic.Add(sr.GetName(i), sr[i].ToString());
                    }
                    list.Add(dic);
                }
                return list;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }
    }
}
